﻿using Oracle.ManagedDataAccess.Client;
using SAO.OracleHelper.IDBHelp;
using SAO.OracleHelper.ReflectionExtensions;
using SAO.OracleHelper.ValiDateModel;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Reflection;
using System.Text;

namespace SAO.OracleHelper
{
    public class OracleSqlHelper : IDBHelper
    {
        private static string _ConnectionString = string.Empty;
        static OracleSqlHelper()
        {
            _ConnectionString = ConfigurationManager.AppSettings["Oracle"];
        }
        public OracleSqlHelper(string connectionString)
        {
            _ConnectionString = connectionString;
        }
        public bool Delete<T>(T t)
        {
            Type type = typeof(T);
            string sql = SqlCache<T>.DeleteSql;
            Func<OracleCommand, bool> func = new Func<OracleCommand, bool>(cmd =>
            {
                string key = type.GetKey();
                OracleParameter sqlParameter = new OracleParameter($"{key}", type.GetProperty(key).GetValue(t) ?? DBNull.Value);
                cmd.Parameters.Add(sqlParameter);
                return cmd.ExecuteNonQuery() > 0;
            });
            return ActionHanlder(func, sql);
        }
        public T Find<T>(int id)
        {
            Type type = typeof(T);
            string sql = SqlCache<T>.FindSql;
            Func<OracleCommand, T> func = new Func<OracleCommand, T>(cmd =>
            {
                T result = default(T);
                string key = type.GetKey();
                OracleParameter sqlParameter = new OracleParameter($"{key}", id);
                cmd.Parameters.Add(sqlParameter);
                OracleDataReader dataReader = cmd.ExecuteReader();
                if (dataReader.HasRows)
                {
                    result = Tran<T>(type, dataReader);
                }
                return result;
            });
            return ActionHanlder(func, sql);
        }
        public bool Insert<T>(T t)
        {
            Type type = typeof(T);
            string sql = SqlCache<T>.InsertSql;
            Func<OracleCommand, bool> func = new Func<OracleCommand, bool>(cmd =>
            {
                var parameterList = type.GetProperties().Select(s => new OracleParameter($"{s.GetName()}", s.GetValue(t) ?? DBNull.Value));
                cmd.Parameters.AddRange(parameterList.ToArray());
                return cmd.ExecuteNonQuery() > 0;
            });
            return ActionHanlder(func, sql);
        }
        public List<T> Query<T>()
        {
            Type type = typeof(T);
            string sql = SqlCache<T>.QuerySql;
            Func<OracleCommand, List<T>> func = new Func<OracleCommand, List<T>>(cmd =>
            {
                List<T> resultList = null;
                OracleDataReader dataReader = cmd.ExecuteReader();
                if (dataReader.HasRows)
                {
                    resultList = Trans<T>(type, dataReader);
                }
                return resultList;
            });
            return ActionHanlder(func, sql);
        }
        public bool UpDate<T>(T t)
        {
            Type type = typeof(T);
            string sql = SqlCache<T>.UpdateSql;
            Func<OracleCommand, bool> func = new Func<OracleCommand, bool>(cmd =>
            {
                var parameterList = type.GetProperties().Select(s => new OracleParameter($"{s.GetName()}", s.GetValue(t) ?? DBNull.Value));
                cmd.Parameters.AddRange(parameterList.ToArray());
                return cmd.ExecuteNonQuery() > 0;
            });
            return ActionHanlder(func, sql);
        }
        private T Tran<T>(Type type, OracleDataReader dataReader)
        {
            dataReader.Read();
            object obj = Activator.CreateInstance(type);
            foreach (PropertyInfo prop in type.GetProperties())
            {
                prop.SetValue(obj, dataReader[prop.Name] is DBNull ? null : dataReader[prop.Name]);
            }
            return (T)obj;
        }
        private List<T> Trans<T>(Type type, OracleDataReader dataReader)
        {
            List<T> objList = new List<T>();
            while (dataReader.Read())
            {
                object obj = Activator.CreateInstance(type);
                foreach (PropertyInfo prop in type.GetProperties())
                {
                    prop.SetValue(obj, dataReader[prop.Name] is DBNull ? null : dataReader[prop.Name]);
                }
                objList.Add((T)obj);
            }
            return objList;

        }
        private T ActionHanlder<T>(Func<OracleCommand, T> func, string sql)
        {
            using (OracleConnection conn = new OracleConnection(_ConnectionString))
            {
                using (OracleCommand command = new OracleCommand(sql, conn))
                {
                    if (conn.State == ConnectionState.Closed)
                    {
                        conn.Open();
                        OracleGlobalization sessionGlob = conn.GetSessionInfo();
                        sessionGlob.Language = "AMERICAN";
                        conn.SetSessionInfo(sessionGlob);
                        //按照名称参数化
                        command.BindByName = true;
                    }
                    return func.Invoke(command);
                }
            }
        }
        public T Find<T>(string sql)
        {
            Type type = typeof(T);
            Func<OracleCommand, T> func = new Func<OracleCommand, T>(cmd =>
            {
                T result = default(T);
                OracleDataReader dataReader = cmd.ExecuteReader();
                if (dataReader.HasRows)
                {
                    result = Tran<T>(type, dataReader);
                }
                return result;
            });
            return ActionHanlder(func, sql);
        }
        public List<T> Query<T>(string sql)
        {
            Type type = typeof(T);
            Func<OracleCommand, List<T>> func = new Func<OracleCommand, List<T>>(cmd =>
            {
                List<T> resultList = null;
                OracleDataReader dataReader = cmd.ExecuteReader();
                if (dataReader.HasRows)
                {
                    resultList = Trans<T>(type, dataReader);
                }
                return resultList;
            });
            return ActionHanlder(func, sql);
        }
        public bool Execute(string sql)
        {
            Func<OracleCommand, bool> func = new Func<OracleCommand, bool>(cmd =>
            {
                return cmd.ExecuteNonQuery() > 0;
            });
            return ActionHanlder(func, sql);
        }
        public object GetSingle(string sql)
        {
            Func<OracleCommand, object> func = new Func<OracleCommand, object>(cmd =>
            {
                object obj = cmd.ExecuteScalar();
                if ((Equals(obj, null)) || (Equals(obj, DBNull.Value)))
                {
                    return null;
                }
                else
                {
                    return obj;
                }
            });
            return ActionHanlder(func, sql);
        }
        public T Get<T, S>(S id)
        {
            Type type = typeof(T);
            string sql = SqlCache<T>.FindSql;
            Func<OracleCommand, T> func = new Func<OracleCommand, T>(cmd =>
            {
                T result = default(T);
                string key = type.GetKey();
                OracleParameter sqlParameter = new OracleParameter($"{key}", id);
                cmd.Parameters.Add(sqlParameter);
                OracleDataReader dataReader = cmd.ExecuteReader();
                if (dataReader.HasRows)
                {
                    result = Tran<T>(type, dataReader);
                }
                return result;
            });
            return ActionHanlder(func, sql);
        }
    }
}
